set echo off
set serveroutput on verify off heading off termout off

define     snapper_sid="&1"
define     debug = nvl('&2','1');
define     ssid_begin=&snapper_sid


define     sid_filter="/**/"
define     inst_filter="/**/"


-- initialize, precompute and determine stuff
var v                      varchar2(100)
var x                      varchar2(10)
var sid_filter             varchar2(4000)
var inst_filter            varchar2(4000)




col snapper_sid           noprint new_value snapper_sid
col sid_filter            noprint new_value sid_filter
col inst_filter           noprint new_value inst_filter



declare
    tmp                    number;
    lv_sid_filter          varchar2(4000);
    lv_inst_filter         varchar2(4000);
    debug                  VARCHAR2 (3)    :='1';
    lv_sid                 varchar2(100);
    v_sql                  varchar2(1000);
    type tmp_sestab is table of gv$session%rowtype index by pls_integer;
    tmp_sessions tmp_sestab;


    function get_filter(str in varchar2) return varchar2
    is
      ret varchar2(1000);
    begin
      if str like '%@%' then 
        --dbms_output.put_line('get_filter:1 str= '||str);
        ret := lower(trim(regexp_replace(substr(str,instr(str,'=')+1), '^(.+)@([[:digit:]\*]+)(.*)', '\1')));
      else
        --dbms_output.put_line('get_filter:2 str= '||str);
        ret := lower(trim(substr(str,instr(str,'=')+1)));
      end if;
      --dbms_output.put_line('get_filter = ' || ret);
      return ret;
    end get_filter;


begin
    -- compute inst_filter
    case 
        when regexp_instr('&ssid_begin','@')           = 0 then 
            lv_inst_filter := '/* inst_filter */ s.inst_id=USERENV(''Instance'')';
        when regexp_instr('&ssid_begin','@\*') > 0 or '&ssid_begin' like '(%' then 
            lv_inst_filter := '/* inst_filter */ 1=1';
        when regexp_instr('&ssid_begin','@\d+')        > 0 then 
            lv_inst_filter := 's.inst_id = ' || regexp_replace('&ssid_begin', '^(.+)@(\d+)(.*)', '\2');
        else
            lv_inst_filter := 's.inst_id=USERENV(''Instance'')';
        --when regexp_instr('&ssid_begin','@\d+')   > 0 then regexp_replace(snapper_sid, '^(.+)@\d+', '\1') || ' AND inst_id = ' || regexp_replace(snapper_sid, '^(.+)@(\d+)(.*)', '\2') 
    end case;

    -- compute sid_filter
    case
        when trim(lower('&ssid_begin')) like 'con_id=%'    then lv_sid_filter   := 's.con_id in ('||get_filter('&ssid_begin')||')';
        when trim(lower('&ssid_begin')) like 'sid=%'       then lv_sid_filter   := 's.sid in ('   ||get_filter('&ssid_begin')||')'; 
        when trim(lower('&ssid_begin')) like 'audsid=%'    then lv_sid_filter   := 's.audsid in ('||get_filter('&ssid_begin')||')'; 
        when trim(lower('&ssid_begin')) like 'user=%'      then lv_sid_filter   := 'lower(username) like '''         ||get_filter('&ssid_begin')||'''';
        when trim(lower('&ssid_begin')) like 'username=%'  then lv_sid_filter   := 'lower(username) like '''         ||get_filter('&ssid_begin')||'''';
        when trim(lower('&ssid_begin')) like 'machine=%'   then lv_sid_filter   := 'lower(machine) like '''          ||get_filter('&ssid_begin')||'''';
        when trim(lower('&ssid_begin')) like 'program=%'   then lv_sid_filter   := 'lower(program) like '''          ||get_filter('&ssid_begin')||'''';
        when trim(lower('&ssid_begin')) like 'service=%'   then lv_sid_filter   := 'lower(service_name) like '''     ||get_filter('&ssid_begin')||'''';
        when trim(lower('&ssid_begin')) like 'module=%'    then lv_sid_filter   := 'lower(module) like '''           ||get_filter('&ssid_begin')||'''';
        when trim(lower('&ssid_begin')) like 'action=%'    then lv_sid_filter   := 'lower(action) like '''           ||get_filter('&ssid_begin')||'''';
        when trim(lower('&ssid_begin')) like 'osuser=%'    then lv_sid_filter   := 'lower(osuser) like '''           ||get_filter('&ssid_begin')||'''';
        when trim(lower('&ssid_begin')) like 'client_id=%' then lv_sid_filter   := 'lower(client_identifier) like '''||get_filter('&ssid_begin')||'''';
        when trim(lower('&ssid_begin')) like 'spid=%'      then lv_sid_filter   := '(s.inst_id,s.paddr) in (select /*+ UNNEST */ inst_id,addr from gv$process where spid in ('||get_filter('&ssid_begin')||'))';
        when trim(lower('&ssid_begin')) like 'ospid=%'     then lv_sid_filter   := '(s.inst_id,s.paddr) in (select /*+ UNNEST */ inst_id,addr from gv$process where spid in ('||get_filter('&ssid_begin')||'))';
        when trim(lower('&ssid_begin')) like 'pid=%'       then lv_sid_filter   := '(s.inst_id,s.paddr) in (select /*+ UNNEST */ inst_id,addr from gv$process where spid in ('||get_filter('&ssid_begin')||'))';
        when trim(lower('&ssid_begin')) like 'qcsid=%'     then lv_sid_filter   := '(s.inst_id,s.sid)   in (select /*+ NO_UNNEST */ inst_id,sid from gv$px_session where qcsid in ('||get_filter('&ssid_begin')||'))';
        when trim(lower('&ssid_begin')) like 'qc=%'        then lv_sid_filter   := '(s.inst_id,s.sid)   in (select /*+ NO_UNNEST */ inst_id,sid from gv$px_session where qcsid in ('||get_filter('&ssid_begin')||'))';
        when trim(lower('&ssid_begin')) like 'all%'        then lv_sid_filter   := '1=1 and type=''USER''';
        when trim(lower('&ssid_begin')) like 'bg%'         then lv_sid_filter   := 'type=''BACKGROUND''';
        when trim(lower('&ssid_begin')) like 'fg%'         then lv_sid_filter   := 'type=''USER''';
        when trim(lower('&ssid_begin')) like 'smon%'       then lv_sid_filter   := 'program like ''%(SMON)%''';
        when trim(lower('&ssid_begin')) like 'pmon%'       then lv_sid_filter   := 'program like ''%(PMON)%''';
        when trim(lower('&ssid_begin')) like 'ckpt%'       then lv_sid_filter   := 'program like ''%(CKPT)%''';
        when trim(lower('&ssid_begin')) like 'lgwr%'       then lv_sid_filter   := 'program like ''%(LG__)%'''; -- 12c multiple adaptive LGWR workers
        when trim(lower('&ssid_begin')) like 'dbwr%'       then lv_sid_filter   := 'regexp_like(program, ''.*\((DBW.|BW..)\).*'', ''i'')';
        when trim(lower('&ssid_begin')) like 'select%'     then lv_sid_filter   := q'{(s.inst_id,s.sid) in (&snapper_sid)}';
        when trim(lower('&ssid_begin')) like '(%'          then lv_inst_filter  := '/* inst_filter2 */ 1=1'; lv_sid_filter := q'{(s.inst_id,s.sid) in (&snapper_sid)}'; 
        else                                                    lv_sid_filter   := '/* sid_filter_else_cond */ s.sid in ('||get_filter('&ssid_begin')||')'; 
    end case;

    :inst_filter := lv_inst_filter;
    :sid_filter  := lv_sid_filter;



exception
    when others then null;
end;
/


select
    :sid_filter  sid_filter,
    :inst_filter inst_filter
from dual;

DECLARE
    v_sql   VARCHAR2 (1000);
    debug   VARCHAR2 (3) := &debug;
    v_sid   varchar2(1000);

    PROCEDURE Execsql (input Varchar2,execsql VARCHAR2, output VARCHAR2)
    IS
    BEGIN
        IF output = '1'
        THEN
            DBMS_OUTPUT.put_line ('Detailed information:' || input);
            DBMS_OUTPUT.put_line ('ExecSQL is:' || execsql);
        ELSE
            DBMS_OUTPUT.put_line (
                TO_CHAR (SYSDATE, 'yyyy-mm-dd hh24:mi:ss') || ':' || execsql);

            EXECUTE IMMEDIATE execsql;
        END IF;
        EXCEPTION
            WHEN OTHERS THEN
              DBMS_OUTPUT.put_line('Find Error on :'||SQLERRM||':'||execsql);
    END Execsql;
BEGIN
        FOR cur_session    IN
            (SELECT * FROM gv$session s
                WHERE    1=1
                and &sid_filter
              )
    LOOP
        BEGIN

        if (cur_session.inst_id <> userenv('instance')) then
          v_sid:=cur_session.sid||','||cur_session.serial#||',@'||cur_session.inst_id;
        else
          v_sid:=cur_session.sid||','||cur_session.serial#;
        end if;

        v_sql :='alter system kill session '|| CHR (39)||v_sid|| CHR (39)|| ' immediate';


        Execsql (cur_session.program||':'||cur_session.username||':'||cur_session.inst_id||':'||cur_session.sid||':'||cur_session.status,v_sql, debug);
        EXCEPTION
            WHEN OTHERS THEN
              DBMS_OUTPUT.put_line('Find Error on :'||SQLERRM||':'||cur_session.sid);
        END;
    END LOOP;
END;
/

undefine     snapper_sid;
undefine     debug;
undefine     ssid_begin;
col snapper_sid            clear
col sid_filter             clear
col inst_filter            clear
set serveroutput off termout on  heading on
